package com.solr.web.controller.quartz;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;

import org.apache.solr.client.solrj.SolrServer;
import org.apache.solr.common.SolrInputDocument;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;

public class myJob {
	
	@Autowired
	private SolrServer solrServer;
	
	@Value("${jdbc.url}")
	private String JDBC_URL;
	
	@Value("${jdbc.username}")
	private String JDBC_USERNAME;
	
	@Value("${jdbc.password}")
	private String JDBC_PASSWORD;
	
	public void execute(){
		Connection conn = null;
        try {
            // 使用MySQL的驱动
            Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动

            System.out.println("成功加载MySQL驱动程序");
            // 一个Connection代表一个数据库连接
            conn = DriverManager.getConnection(JDBC_URL,JDBC_USERNAME,JDBC_PASSWORD);
            
            Date dt=new Date();
            Calendar calendar = new GregorianCalendar();
            calendar.setTime(dt);
            calendar.add(calendar.DATE,-1);
            Date time = calendar.getTime();
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    		String dateString = formatter.format(time);
            
            String sql_count="select count(*) from t_news_zw where createTime >= '"+dateString+"'";
            PreparedStatement pstmCount = conn.prepareStatement(sql_count);
			ResultSet rsCount = pstmCount.executeQuery(sql_count);
			rsCount.next();
			int totalCount=rsCount.getInt(1);
			System.out.println(sql_count);
            System.out.println("============totalCount==="+totalCount);
            
            int pageSize = 5000;
            
            int max=totalCount%pageSize==0 ? totalCount/pageSize : totalCount/pageSize+1;
            //SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            
            SolrInputDocument document;
            for(int i=0;i<max;i++){
	            //String sql = "select a.id,a.keyword,a.title,a.href,a.publishtime,a.servicetype,b.area from t_news_zw a left join t_news_keyword_area_map b on a.keyword=b.keyword where a.publishtime >= '"+dateString+"' limit "+ i*pageSize + " , "+pageSize;
	            String sql = "select a.id,a.keyword,a.title,a.href,a.rate,a.publishtime,a.servicetype,b.area from t_news_zw a LEFT JOIN (SELECT distinct keyword,area FROM t_news_keyword_area_map) b on a.keyword=b.keyword where a.createTime >= '"+dateString+"' limit "+ i*pageSize + " , "+pageSize;
	            System.out.println("==================="+sql);
	            PreparedStatement pstm = conn.prepareStatement(sql);
	                
	            // Statement里面带有很多方法，比如executeUpdate可以实现插入，更新和删除等
	            // executeQuery会返回结果的集合，否则返回空值
	            ResultSet rs = pstm.executeQuery();
	                
	             List<SolrInputDocument> docs = new ArrayList<>();
				 
				 while (rs.next()) {
	            	 document = new SolrInputDocument();
					
	            	 document.addField("id", rs.getLong("id"));
	            	 
	            	 String keyword = rs.getString("keyword");
					  if (keyword != null && !"".equals(keyword)) {
						  document.addField("keyword", keyword);
					  }else{
						  document.addField("keyword", "");
					  }
					  
					  String title = rs.getString("title");
					  if (title != null && !"".equals(title)) {
						  document.addField("title", title);
					  }else{
						  document.addField("title", "");
					  }
					  
					  String href = rs.getString("href");
					  if (href != null && !"".equals(href)) {
						  document.addField("href", href);
					  }else{
						  document.addField("href","");
					  }
					  
					  document.addField("rate", rs.getDouble("rate"));
					  
					 Timestamp timestamp = rs.getTimestamp("publishtime");
					  if (timestamp != null) {
						  String date = formatter.format(timestamp);
						  document.addField("publishtime", date);
					  }else {
						  document.addField("publishtime", "");
					  }
					  
					  Integer servicetype = rs.getInt("servicetype");
					  if (servicetype != null) {
						  document.addField("servicetype", rs.getInt("servicetype"));
					  }
					  
					  String area = rs.getString("area");
					  if (area != null && !"".equals(area)) {
						  document.addField("area", area);
					  }else{
						  document.addField("area","");
					  }
					 
					 docs.add(document);
				}
				 
				 // 调用SolrServer的api方法将数据导入到索引库
				 solrServer.add(docs);
				 // 提交
				 solrServer.commit();
            }
                
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
        }
	}
		
}
